<?php
/* This class provides functionality to insert quotes from text files
 * into the database
 * 
 */

class fileToDb {
  public $eoddatadir;
  public $exchangenames=array();
  public $dbinfo=array();
  public $fileinfo=array();
  public $missingInDb=array();
  public $overlapping=array(); // young files that are used to overwrite contents already imported into db 
  public $refsyms = array(     // this symbols are used to determine date boundaries
    "INDEX" => "DJI.IDX",
    "COMEX" => "GC",
    "CBOT"  => "RR",
    "FOREX" => "USDJPY",
    "NASDAQ"=> "AAPL",
    "NYSE"  => "IBM"
  );
  private $db;
  
  function __construct($db, $eoddatadir) {
    $this->db=$db;
    $this->eoddatadir=$eoddatadir;
    // fill object with information
    $this->availableExchangesInDB();
    $this->availableFiles();
    $this->DbDateBoundaries();
    $this->determineMissing();
    $this->determineOverlapping();
  }
  
  /* a price bar after parsing text document (=$contents)
    [1510] => Array
        (
            [0] => ZOC
            [1] => 20120717
            [2] => 96.86
            [3] => 97.4
            [4] => 95.89
            [5] => 96.82
            [6] => 0
        )
   */
  
  // $set="missing" or $set="overlapping"
  // $method="noreplace" doesn't replace price information if price 
  // already exists. $method="replace" replaces prices
  public function insertMissing($set="missing", $method="noreplace") {
    if ($set=="missing") {
      $myset=$this->missingInDb;
    } elseif ($set="overlapping") {
      $myset=$this->overlapping;
    }
    foreach ($myset as $exchange=>$value) {
      foreach ($value as $dt) {
        $contents = $this->readTextFile($exchange, $dt);
        $newsymbols=array();
        $insertcount=0;
        $updatecount=0;
        $error=false;
        foreach ($contents as $c) {
          // ask db if symbol already exists or is new
          $symc = $c[0];
          if ($exchange=="INDEX") $symc.=".IDX";
          $res=$this->db->get_symbol($symc, $exchange);
          if (empty($res)) {
            // add symbol, then add price
            $newsymbols[]=$symc;
            $exid = $this->db->get_exchange_id($exchange);
            $this->db->insert_symbol($symc, "", $exid['id']);
            $res=$this->db->get_symbol($symc, $exchange);
          }
          $id=$res[0]['id'];
          
          // check db if price is already stored
          $res=$this->db->get_price($id, $this->formatDate($c[1]));
          if (empty($res)) {
            if (sizeof($c)==7) {
              $this->db->insert_price($id, $this->formatDate($c[1]), $c[2], $c[3], $c[4], $c[5], $c[6]);
              $insertcount++;
            } elseif (sizeof($c)==8) {
              $this->db->insert_price($id, $this->formatDate($c[1]), $c[2], $c[3], $c[4], $c[5], $c[6], $c[7]);
              $insertcount++;
            }
          } elseif($method=="noreplace") {
            $error=true;  // don't overwrite since price already stored
          } elseif($method=="replace") {
            if (sizeof($c)==7) {
              $this->db->update_price($id, $this->formatDate($c[1]), $c[2], $c[3], $c[4], $c[5], $c[6]);
              $updatecount++;
            } elseif (sizeof($c)==8) {
              $this->db->update_price($id, $this->formatDate($c[1]), $c[2], $c[3], $c[4], $c[5], $c[6], $c[7]);
              $updatecount++;
            }
          }
        }

        // log this
        if ($error) {
          $remark="insert failed at at least one symbol because price already existed";
        } else {
          $remark="insert $set successful";
          if (!empty($newsymbols)) {
            $remark.=". added symbols: ";
            foreach ($newsymbols as $s) {
              $remark.=" $s";
            }
          }
        }
        $this->db->insert_DbWriter_logentry("insertIntoDb", $error, $exchange."_".$c[1].".txt", $insertcount, $updatecount, sizeof($newsymbols), $remark);
      }
    }
  }
  
  // format of text file:
  // AH,20120604,126.87,127.6,126.8,127.54,867,9230
  // reads text files and returns array with data
  private function readTextFile($exchange, $date) {
    $retvar=array();
    $contents=file($this->eoddatadir."/".$exchange."_".$date.".txt", FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
    foreach ($contents as $line) {
      $exp=explode(",", trim($line));
      $retvar[]=$exp;
    }
    return($retvar);
  }
  
  // determine available exchanges in DB
  private function availableExchangesInDB() {
    $exchanges = $this->db->get_exchanges();
    foreach ($exchanges as $exchange) {
      $this->exchangenames[]=$exchange['name'];
    }
  }
  
  // determines available quote files on local directory
  private function availableFiles() {
    if (is_dir($this->eoddatadir)) {
      $dh = opendir($this->eoddatadir);
      if ($dh) {
        while (($file = readdir($dh)) !== false) {
          if (strpos($file, ".txt") > 0 & strpos($file, "_") > 0 & filetype($this->eoddatadir."/".$file)=="file") {
            $exp1=explode("_",$file);
            if (in_array($exp1[0], $this->exchangenames)) {
              $exp2=explode(".txt", $exp1[1]);
              $fileinfo[$exp1[0]][]=$exp2[0];
            }
          }
        }
        closedir($dh);
      }
      // sort array
      foreach ($fileinfo as $key=>$value) {
        sort($value);
        $this->fileinfo[$key]=$value;
      }
    } else {
      echo "Warning: eod data dir doesn't exist! ($this->eoddatadir)";
    }
  }
  
  private function DbDateBoundaries() {
    foreach ($this->exchangenames as $exchange) {
      $stockid = $this->db->get_symbol($this->refsyms[$exchange], $exchange);
      $dateboundaries = $this->db->get_date_boundaries($stockid[0]['id']);
      $this->dbinfo[$exchange]['first'] = str_replace("-", "", $dateboundaries[0]);
      $this->dbinfo[$exchange]['last']  = str_replace("-", "", $dateboundaries[1]);
    }
  }
  
  private function determineMissing() {
    foreach ($this->exchangenames as $exchange) {
      $this->missingInDb[$exchange]=array();
      if (array_key_exists($exchange, $this->fileinfo)) {
        foreach ($this->fileinfo[$exchange] as $file) {
          if ($file > $this->dbinfo[$exchange]['last']) {
            $this->missingInDb[$exchange][] = $file;
          }
        }        
      }
    }    
  }
  
  // very recent files (of the same day or the period before)
  // have to be inserted into db again, because EODDATA 
  // adjusts the contents of the files subsequently
  private function determineOverlapping($overlap=2) {
    $temp=array();
    foreach ($this->exchangenames as $exchange) {
      $temp[$exchange]=array();
      if (array_key_exists($exchange, $this->fileinfo)) {
        foreach ($this->fileinfo[$exchange] as $file) {
          if ($file <= $this->dbinfo[$exchange]['last']) {
            $temp[$exchange][] = $file;
          }
        }     
      }      
    }
    foreach ($temp as $key => $tempex) {
      $this->overlapping[$exchange]=array();
      sort($tempex);
      for ($i=0; $i<$overlap; $i++) {
        $this->overlapping[$key][]=$tempex[sizeof($tempex)-$overlap+$i];
      }
    }
  }
  
  private function formatDate($s) {
    return (substr($s,0,4)."-".substr($s,4,2)."-".substr($s,6,2));
  }
  
}

?>
